/**
 * 
 */
package com.td.framework.webmanager.ymgn.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.td.framework.utils.DateUtils;
import com.td.framework.webmanager.domain.AppsysQxfwjlgx;
import com.td.framework.webmanager.domain.AppsysYmgn;
import com.td.framework.webmanager.domain.AppsysYmgnBean;
import com.td.framework.webmanager.domain.AppsysYmgnan;
import com.td.framework.webmanager.domain.AppsysYmxzfwjl;
import com.td.framework.webmanager.domain.AppsysYmxzfwjlBean;
import com.td.framework.webmanager.domain.YmxztjBean;
import com.td.framework.webmanager.ymgn.dao.YmgnDao;

/**
 * 
 * Title:页面功能数据层实现类 Description: 实现数据层页面功能处理方法 Copyright: Copyright (c) 2008
 * Company 北京水软科技有限公司
 * 
 * @author FLT006
 * @version 1.0
 */
public class YmgnDaoImpl extends HibernateDaoSupport implements YmgnDao {
	private static final Log log = LogFactory.getLog(YmgnDaoImpl.class);
	private String sql = "";// sql语句;
	private List<AppsysYmgn> ymgnList = null;// 页面功能List;
	private List<AppsysYmgnan> ymgnanList = null;// 功能按钮List;
	private boolean delStatus;// 删除记录标志 true:彻底删除；false:象征性删除;
	private boolean flag = false;// 操作状态;

	/**
	 * @toDo:保存页面功能信息;
	 * @param ymgn
	 * @return boolean;
	 */
	public boolean addAppsysYmgnan(AppsysYmgnan ymgnan) {
		// TODO Auto-generated method stub

		try {
			if (ymgnan != null) {
				ymgnan.setScbz(new Long(0));
				ymgnan.setCjrq(DateUtils.currentDate());
				if (ymgnan.getCjr() == null) {
					ymgnan.setCjr("admin");
				}
			}
			this.getHibernateTemplate().save(ymgnan);
			flag = true;
		} catch (RuntimeException re) {
			flag = false;
			log.error("Exception： YmgnDaoImpl+addAppsysYmgn", re);
			throw re;
		}
		return flag;
	}

	/**
	 * @toDo:删除页面功能信息,支持批量删除;
	 * @param anbhs
	 * @return boolean;
	 */
	public boolean deleteAppsysYmgnan(String[] anbhs) {
		// TODO Auto-generated method stub
		sql = "update appsys_ymgnan t set t.scbz=1 where 1=1 and ";
		if (delStatus) {
			sql = "delete from appsys_ymgnan t where 1=1 and ";
		}
		String condition = " t.anbh in ";// 删除条件;
		String childCon = " ( ";
		for (int i = 0; i < anbhs.length; i++) {
			if (i == anbhs.length - 1) {
				childCon += anbhs[i] + " ) ";
			} else {
				childCon += anbhs[i] + ", ";
			}
		}
		condition += childCon+" or t.sjbh in "+childCon;
		sql += condition;
		try {
			Connection con = this.getSession().connection();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.executeUpdate();
			pstmt.close();
			con.close();
			flag = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			flag = false;
			log.error("Exception： YmgnDaoImpl+deleteAppsysYmgn", e);
		}
		return flag;
	}

	/**
	 * @toDo:查询所有的页面功能信息;
	 * @return list;
	 */
	@SuppressWarnings("unchecked")
	public List<AppsysYmgnan> findAll() {
		// TODO Auto-generated method stub
		sql = "from com.td.framework.webmanager.domain.AppsysYmgnan t "
				+ "where t.scbz=0 order by t.anbh";
		ymgnanList = this.getHibernateTemplate().find(sql);
		return ymgnanList;
	}

	/**
	 * @toDo:得到与权限结合的功能List;
	 * @param qxbh
	 * @param ymbh
	 * @param type
	 *            1 为页面功能, 2 为页面限制字段,
	 *            3 为模块添加时待页面功能
	 *            4 为查询模块页面功能
	 * @return list;
	 */
	@SuppressWarnings("unchecked")
	public List<AppsysYmgnBean> findAppsysYmgnBean(Long qxbh, String ymbh,
			int type) {
		sql = " select t.gnbh ,t.gnmc ,nvl(a.id,-1) flag,(1) qxbz,t.sjbh " +
				"from (select t.anbh as gnbh,t.anmc as gnmc,t.sjbh " +
				"from appsys_ymgnan t  " +
				"where t.scbz=0 and t.ymbh='"+ymbh+"'  ) t left join (select a.id,a.lxbhz,a.scbz " +
				"from appsys_qxgx a where a.scbz=0 and a.qxbh=  " +qxbh+
				" and a.ymbh='"+ymbh+"' and a.qxbz=1 ) a  on t.gnbh =a.lxbhz  " +
				" order by t.sjbh,t.gnbh ";
		if (type == 2) {
			sql = " select t.zdbh as gnbh ,t.zdmc as gnmc,nvl(a.id,-1) flag,(2) qxbz,(-1) as sjbh "
					+ "from appsys_ymxzzd t "
					+ " left join ( select a.id,a.lxbhz,a.scbz from appsys_qxgx a "
					+ " where a.scbz=0 and a.qxbh="
					+ qxbh
					+ "  and a.ymbh='"
					+ ymbh
					+ "' and a.qxbz=2 "
					+ ") a on t.zdbh =a.lxbhz where t.scbz=0 and t.ymbh='"
					+ ymbh + "' order by t.zdbh ";
		}
		else if(type == 3)
		{
			sql = " select t.gnbh ,t.gnmc ,nvl(a.id,-1) flag,(1) qxbz,(-1) as sjbh from appsys_ymgn t " +
					" left join (  select b.id,b.gnbh,b.ymbh from appsys_ymygnsgx b " +
					" where b.ymbh='"+ymbh+"' and b.scbz=0 ) a " +
					" on t.gnbh =a.gnbh where t.scbz=0 order by t.gnbh";
		}
		else if(type == 4)
		{
			sql = "select t.gnbh ,t.gnmc ,nvl(b.id,-1) flag,(1) qxbz,(-1) as sjbh " +
					"from appsys_ymgn t ,appsys_ymygnsgx b " +
					"where b.ymbh='"+ymbh+"' and b.scbz=0 " +
					"and t.gnbh =b.gnbh and t.scbz=0 " +
					"order by t.gnbh";
		}
		Session session = this.getSession();
		Connection con = session.connection();
		AppsysYmgnBean appsysYmgnBean = new AppsysYmgnBean();
		List<AppsysYmgnBean> list = new ArrayList();
		ResultSet rs;
		try {
			Statement st = con.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				appsysYmgnBean = new AppsysYmgnBean();
				appsysYmgnBean.setGnbh(rs.getLong("gnbh"));
				appsysYmgnBean.setGnmc(rs.getString("gnmc"));
				appsysYmgnBean.setFlag(rs.getLong("flag"));
				appsysYmgnBean.setQxbz(rs.getLong("qxbz"));
				appsysYmgnBean.setSjbh(rs.getLong("sjbh"));
				list.add(appsysYmgnBean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.error("Exception： YmgnDaoImpl+findAppsysYmgnBean", e);
		} finally {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				log.error("Exception： YmgnDaoImpl+findAppsysYmgnBean", e);
			}
			//session.close();
		}
		return list;
	}

	/**
	 * @toDo:保存用户组页面级功能权限;
	 * @param qxbh
	 * @param ymbh
	 * @param gnbhs
	 * @param qxfwjlgxs
	 * @return boolean;
	 */
	public boolean addYmgnRight(String qxbh, String ymbh, String[] gnbhs,
			List<AppsysQxfwjlgx> qxfwjlgxs) {
		boolean flag = false;
		String sql = "";
		Connection con = null;
		Session session = getSession();
		try {

			con = session.connection();
			Statement st = con.createStatement();
			con.setAutoCommit(false);
			if (!qxbh.equals("")) {
				for (int j = 0; j < qxbh.split(",").length; j++) {
					// 删除页面级功能
					sql = "delete from appsys_qxgx t where t.qxbh = "
							+ qxbh.split(",")[j] + " and t.ymbh = '" + ymbh
							+ "'";
					st.addBatch(sql);
					// 保存页面功能、限制字段权限
					if (gnbhs != null) {
						for (int i = 0; i < gnbhs.length; i++) {
							sql = "insert into appsys_qxgx t (t.id,t.qxbh,t.lxbhz,t.ymbh,t.qxbz,t.scbz) "
									+ "values (seq_appsys_qxgx.nextval,"
									+ qxbh.split(",")[j]
									+ ","
									+ gnbhs[i].split(",")[0]
									+ ",'"
									+ ymbh
									+ "'," + gnbhs[i].split(",")[1] + ",0)";
							st.addBatch(sql);

						}
					}
					// 删除限制访问记录功能
					sql = "delete from appsys_qxfwjlgx t where t.qxbh = "
							+ qxbh.split(",")[j] + " and t.ymbh = '" + ymbh
							+ "'";
					st.addBatch(sql);
					// 保存限制访问记录权限
					if (qxfwjlgxs!=null&&!qxfwjlgxs.isEmpty()) {
						for (AppsysQxfwjlgx appsysQxfwjlgx : qxfwjlgxs) {
							if (!(appsysQxfwjlgx.getXztj() + "").equals("")
									&& !(appsysQxfwjlgx.getXstj() + "")
											.equals("")) {
								sql = "insert into appsys_qxfwjlgx t (t.id,t.qxbh,t.ymbh,t.zdywmc,"
										+ "t.xzbh,t.xztj,t.xstj,t.scbz,t.sjlx) "
										+ "values (seq_appsys_qxfwjlgx.nextval,"
										+ qxbh.split(",")[j]
										+ ",'"
										+ ymbh
										+ "','"
										+ appsysQxfwjlgx.getZdywmc()
										+ "',"
										+ appsysQxfwjlgx.getXzbh()
										+ ",'"
										+ appsysQxfwjlgx.getXztj()
										+ "','"
										+ appsysQxfwjlgx.getXstj()
										+ "',0,"+appsysQxfwjlgx.getSjlx()+")";
								st.addBatch(sql);
							}
						}
					}
				}
			}
			st.executeBatch();
			con.commit();
			con.setAutoCommit(true);
			flag = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			//e.printStackTrace();
			flag = false;
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				log.error("Exception： QxxxDaoImpl+addYmgnRight", e);
			}
		} finally {

			try {
				if (con != null) {
					con.close();
				}
				//session.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				flag = false;
				log.error("Exception： QxxxDaoImpl+addYmgnRight", e);
			}

		}
		return flag;
	}

	/**
	 * @toDo:通过权限与页面功能关系表查询功能按钮信息
	 * @param qxbh
	 * @param ymbh
	 * @return list;
	 */
	@SuppressWarnings("unchecked")
	public List findByQxgx(Long qxbh, String ymbh) {

		sql = " from com.td.framework.webmanager.domain.AppsysYmgn t "
				+ "where t.scbz=0 order by t.gnbh ";

		List list = this.getHibernateTemplate().find(sql);
		return list;
	}

	/**
	 * @toDo:得到总记录数;
	 * @return rows;
	 */
	@SuppressWarnings("unchecked")
	public int getRows(String anmc,String ymbh) {
		sql = "select t.anbh, t.ymbh,t.anmc as anmc, t.mblx , t.czmb,  t.tplj , " +
				" t.tpgd , t.tpkd , t.xsfs , t.zcdku , t.zcdgs , t.ztxx , t.sjbh , " +
				"t.gnms, t.scbz , t.jibie , t.cjr , t.cjrq from (select t.anbh, " +
				"t.ymbh,a.sjmc||t.anmc as anmc, t.mblx , t.czmb,  t.tplj ,  t.tpgd , t.tpkd ," +
				"t.xsfs , t.zcdku , t.zcdgs , t.ztxx , t.sjbh , t.gnms, t.scbz , t.jibie ," +
				"t.cjr , t.cjrq  from appsys_ymgnan t  left join ( select a.anbh," +
				"nvl(a.anmc,'' )||'->' as sjmc from appsys_ymgnan a " +
				"where a.scbz=0 and a.ymbh='"+ymbh+"') a  on t.sjbh=a.anbh " +
				"where t.scbz=0 and t.ymbh='"+ymbh+"' order by anmc,t.anbh ) t ";
		if (!anmc.trim().equals("") && !anmc.trim().equals("null")) {
			sql += " where t.anmc like '%"+anmc.trim()+"%'";
		}
		int rows = 0;
		Session session = this.getSession();
		SQLQuery query = session.createSQLQuery(sql);
		List list = query.list();
		rows = list.size();
		return rows;
	}
	/**
	 * @toDo:通过功能名称查询页面功能信息
	 * @param pageSize,
	 *            startRow
	 * @return list;
	 */
	@SuppressWarnings("unchecked")
	public List<AppsysYmgnan> findByAnmc(int pageSize, int startRow) {
		Session session = getSession();
		SQLQuery query = session.createSQLQuery(sql).addEntity(AppsysYmgnan.class);
		query.setFirstResult(startRow);
		query.setMaxResults(pageSize);
		ymgnanList = query.list();
		//session.close();
		return ymgnanList;
	}

	/**
	 * @toDo:通过功能编号查询页面功能信息;
	 * @param gnbh
	 * @return AppsysYmgn;
	 */
	public AppsysYmgnan getAppsysYmgnan(Long anbh) {
		// TODO Auto-generated method stub
		AppsysYmgnan ymgn = (AppsysYmgnan) this.getHibernateTemplate().get(
				AppsysYmgnan.class, anbh);
		return ymgn;
	}

	/**
	 * @toDo:更新页面功能信息;
	 * @param ymgn
	 * @return boolean;
	 */
	public boolean updateAppsysYmgnan(AppsysYmgnan ymgn) {
		// TODO Auto-generated method stub
		try {
			this.getHibernateTemplate().update(ymgn);
			flag = true;
		} catch (RuntimeException re) {
			flag = false;
			log.error("Exception： YmgnDaoImpl+updateAppsysYmgn", re);
			throw re;
		}
		return flag;
	}

	/**
	 * @toDo:验证功能名称是否重复;
	 * @param gnmc
	 * @param gnbh
	 * @return boolean;
	 */
	@SuppressWarnings("unchecked")
	public boolean checkYmgn(String ymbh ,String gnmc, String anbh) {
		boolean flag = false;
		sql = "from com.td.framework.webmanager.domain.AppsysYmgnan t"
				+ " where t.ymbh='"+ymbh+"' and t.anmc='" + gnmc.trim() + "' ";
		if (!anbh.equals("") && !anbh.equals("null")) {
			sql += " and t.anbh != " + anbh;
		}
		ymgnList = this.getHibernateTemplate().find(sql);
		if (!ymgnList.isEmpty()) {
			flag = true;
		}
		return flag;
	}
	/**
	 * toDo:查询限制访问记录对象信息;
	 * @param xzbh
	 * @return AppsysYmxzfwjl;
	 */
	public AppsysYmxzfwjl getAppsysYmfwjl(Long xzbh) {
		// TODO Auto-generated method stub
		AppsysYmxzfwjl appsysYmxzfwjl = null;
		appsysYmxzfwjl = (AppsysYmxzfwjl)this.getHibernateTemplate().get(AppsysYmxzfwjl.class, xzbh);
		return appsysYmxzfwjl;
	}
	/**
	 * toDo:通过权限编号、页面编号查询限制访问记录条件
	 * 
	 * @param qxbh
	 * @param ymbh
	 * @return list;
	 */
	@SuppressWarnings("unchecked")
	public List<AppsysYmxzfwjlBean> findAppsysYmxzfwjlBean(Long qxbh,
			String ymbh) {
		List<AppsysYmxzfwjlBean> list = new ArrayList();
		Session session = this.getSession();
		Connection con = session.connection();
		AppsysYmxzfwjlBean appsysYmxzfwjlBean = new AppsysYmxzfwjlBean();
		ResultSet rs;
		sql = "select t.xzbh,t.zdmc,t.zdywmc,t.sjlx ,t.ymbh, a.xztj,a.xstj "
				+ "from appsys_ymxzfwjl t left join (select b.xzbh,b.xztj,b.xstj "
				+ "from appsys_qxfwjlgx b where b.scbz=0 and b.qxbh= " + qxbh
				+ " and b.ymbh='" + ymbh
				+ "' ) a on a.xzbh=t.xzbh where t.scbz=0 and t.ymbh='" + ymbh
				+ "' order by t.sjlx,t.xzbh ";
		try {
			Statement st = con.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				appsysYmxzfwjlBean = new AppsysYmxzfwjlBean();
				appsysYmxzfwjlBean.setXzbh(rs.getLong("xzbh"));
				appsysYmxzfwjlBean.setZdmc(rs.getString("zdmc"));
				appsysYmxzfwjlBean.setZdywmc(rs.getString("zdywmc"));
				appsysYmxzfwjlBean.setSjlx(rs.getLong("sjlx"));
				appsysYmxzfwjlBean.setYmbh(rs.getString("ymbh"));
				appsysYmxzfwjlBean.setXztj(rs.getString("xztj"));
				appsysYmxzfwjlBean.setXstj(rs.getString("xstj"));
				list.add(appsysYmxzfwjlBean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.error("Exception： YmgnDaoImpl+findAppsysYmxzfwjlBean", e);
		} finally {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				log.error("Exception： YmgnDaoImpl+findAppsysYmxzfwjlBean", e);
			}
			//session.close();
		}
		return list;
	}

	/**
	 * toDo:通过权限编号、限制条件编号查询条件列表
	 * @param qxbh
	 * @param xzbh
	 * @param conSql 
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<YmxztjBean> findYmxztjBean(Long qxbh, String xzbh,String conSql) {
		List<YmxztjBean> list = null;
		sql = "select t.conKey ,t.conValue,sjbh ,nvl(a.id,-1) as isChecked " +
				"from (   "+conSql+" ) t left join (  select t.id,t.xztj  " +
				"from appsys_qxfwjlgx t   where t.xzbh ="+xzbh+" and t.qxbh="+qxbh+" ) a " +
				"on instr(','||a.xztj||',',','||t.conkey||',',1)>0 order by t.conKey "; 
		Connection con = null;
		Session session = getSession();
		try {
			con = session.connection();
			Statement st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			ResultSet rs = st.executeQuery(sql);
			YmxztjBean ymxztjBean = null;
			list = new ArrayList();
			while(rs.next())
			{
				ymxztjBean = new YmxztjBean();
				ymxztjBean.setConKey(rs.getString("conKey"));
				ymxztjBean.setConValue(rs.getString("conValue"));
				ymxztjBean.setSjbh(rs.getString("sjbh"));
				ymxztjBean.setIsChecked(rs.getInt("isChecked"));
				list.add(ymxztjBean);
			}
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			flag = false;
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				log.error("Exception： QxxxDaoImpl+findYmxztjBean", e);
			}
		} finally {
			try {
				if (con != null) {
					con.close();
				}
				//session.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				flag = false;
				log.error("Exception： QxxxDaoImpl+findYmxztjBean", e);
			}

		}
		return list;
	}

	/**
	 * @toDo:get删除记录标志;
	 * @return the delStatus
	 */
	public boolean isDelStatus() {
		return delStatus;
	}

	/**
	 * @toDo:set删除记录标志;
	 * @param delStatus
	 *            the delStatus to set
	 */
	public void setDelStatus(boolean delStatus) {
		this.delStatus = delStatus;
	}
}
